package mahi.jdbc;

//STEP 1. Import required packages
import java.sql.*;


/*
 * 
 * 
 *  Establishing a connection.
   	Create a statement.
	Execute the query.
	Process the ResultSet object.
	Close the connection.
 * 
 * 
 * 
 * 
 * insert/update/delete/query
	Prepared Statement
	Calalble Stattement
	autocommit
	batch
	tx (ACID) (savepoint/rollback)
	
	A - Atomocity (smallest minumun operation that cannt be breaken down) automcommit(false) , commit()
	C - Consistency (Propogation) (required, )
	I - Isolation
	D - Durability 
	
	
	Required: Create a new tx (if does not eixts)
	RequiresNew: Create a new tx always
	Never
	Mandatory: an existsing tx is required
	Not Supported: pause an existing tx 
	
	
	tx1 tx2
	
	tx1
	insert()
	{
		
		//pause the tx1
		delete()
	}
	delete()
	
	
	tabel emp
	tx ret(mahender  - 25) +1 26 X  25    lock
	tx2 26+1 =27
	
	
	25 -> 27
	readlock
	writelock
	
		emp-classid
	tx1   2		4  ->6
	tx2    2   4  comiited
	
	
	phantomread (count of rows)
	emp 2
	tx1 2  3
	tx2 2  (insert/delete) 3
 * 
 */
public class JDBCExample {
 // JDBC driver name and database URL
 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
 static final String DB_URL = "jdbc:mysql://localhost/test";

 //  Database credentials
 static final String USER = "root";//"username";
 static final String PASS = "";//"password";
 
 public static void main(String[] args) {
 Connection conn = null;
 Statement stmt = null;
 try{
    //STEP 2: Register JDBC driver
    Class.forName("com.mysql.jdbc.Driver");

    //STEP 3: Open a connection
    System.out.println("Connecting to database...");
    conn = DriverManager.getConnection(DB_URL,USER,PASS);

    //STEP 4: Execute a query
    System.out.println("Creating statement...");
    stmt = conn.createStatement();
   
    String sql;
    sql = "SELECT id, name, address FROM emp";
    ResultSet rs = stmt.executeQuery(sql);
    
    
    
   

    //STEP 5: Extract data from result set
    while(rs.next()){
       //Retrieve by column name
       int id  = rs.getInt("id");
       String name = rs.getString("name");
       String address = rs.getString("address");

       //Display values
       System.out.print("ID: " + id);
      
       System.out.print(", name: " + name);
       System.out.println(", address: " + address);
    }
    
//    String insertSql;
//    insertSql = "INSERT INTO emp(id, name) VALUES ('3', 'dfgvdfg')";
//    boolean bool =  stmt.execute(insertSql);
    
    
    String delSql;
    delSql = "DELETE FROM emp WHERE id='4';";
    boolean bool1 =  stmt.execute(delSql);
    System.out.println(bool1);
    
    
    /*
     * Calling a stored Procedure
     * CallableStatement cs = con.prepareCall("{call MY_SAMPLE_STORED_PROC}");
	 * ResultSet rs = cs.executeQuery();
     */
    
    CallableStatement cs = conn.prepareCall("{call MY_SAMPLE_STORED_PROC}");
	  rs = cs.executeQuery();
	  while(rs.next()){
	       //Retrieve by column name
	       int id  = rs.getInt("id");
	       String name = rs.getString("name");
	       String address = rs.getString("address");

	       //Display values
	       System.out.print("ID: " + id);
	      
	       System.out.print(", name: " + name);
	       System.out.println(", address: " + address);
	    }
	 
    //STEP 6: Clean-up environment
    rs.close();
    stmt.close();
    conn.close();
 }catch(SQLException se){
    //Handle errors for JDBC
    se.printStackTrace();
 }catch(Exception e){
    //Handle errors for Class.forName
    e.printStackTrace();
 }finally{
    //finally block used to close resources
    try{
       if(stmt!=null)
          stmt.close();
    }catch(SQLException se2){
    }// nothing we can do
    try{
       if(conn!=null)
          conn.close();
    }catch(SQLException se){
       se.printStackTrace();
    }//end finally try
 }//end try
 System.out.println("Goodbye!");
}//end main
}//end FirstExample
